/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.icee.myth.manager.db;

import com.icee.myth.manager.config.ServerConfig;
import com.icee.myth.manager.server.DBCharInfo;
import com.icee.myth.manager.server.TopLevelPlayer;
import com.icee.myth.manager.web.PlayerInfo;
import com.icee.myth.manager.web.WebServer;
import com.icee.myth.utils.Consts;
import com.icee.myth.utils.LogConsts;
import com.icee.myth.utils.MLogger;
import com.icee.myth.utils.StackTraceUtil;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;

/**
 * mysql操作
 * @author yangyi
 */
public class DbHandler {

    private String dbHost;  // 数据库地址
    private String dbName;  // 数据库名
    public Connection connection;  // 数据库连接

    public DbHandler(String dbHost, String dbName) {
        this.dbHost = dbHost;
        this.dbName = dbName;
    }

     public boolean connectToDB() {
        try {
            if (connection == null || connection.isClosed()) {
                Class.forName("com.mysql.jdbc.Driver");
                connection = DriverManager.getConnection("jdbc:mysql://" + dbHost + "/" + dbName + "?useUnicode=true&characterEncoding=UTF-8", "mythtest", "jkY5qmGKVcRs4nST");
            }
            return true;
        } catch (SQLException ex) {
            System.err.println("Connect to database error : " + StackTraceUtil.getStackTrace(ex));
        } catch (ClassNotFoundException ex) {
            System.err.println("Connect to database error : " + StackTraceUtil.getStackTrace(ex));
        }

        return false;
    }

     public void close() {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException ex) {
            System.err.println("close database error : " + StackTraceUtil.getStackTrace(ex));
        }
     }
     
     public boolean updateForbinTalkTime(String passport, int time) {
        Statement stmt = null;
//        ResultSet rs = null;
        try {
            if (connectToDB()) {
                final String sql = "update thirdpartyuser set forbiddentalkendtime = from_unixtime(" + time + ") where thirdpartyUserId ='" + passport + "';";
                stmt = connection.createStatement();

                if (stmt.executeUpdate(sql) == 1) {
                    return true;
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
//            if (rs != null) {
//                try {
//                    rs.close();
//                } catch (SQLException ex) {
//                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//                }
//            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return false;
    }

     public boolean updateForbinLoginTime(String passport, int endTime) {
        Statement stmt = null;
//        ResultSet rs = null;
        try {
            if (connectToDB()) {
                String sql = "update thirdpartyuser set forbiddenloginendtime = from_unixtime(" + endTime + ") where thirdpartyUserId ='" + passport + "';";
                stmt = connection.createStatement();

                if (stmt.executeUpdate(sql) == 1) {
                    return true;
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
//            if (rs != null) {
//                try {
//                    rs.close();
//                } catch (SQLException ex) {
//                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
//                }
//            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return false;
    }

     public PlayerInfo getPlayerInfoFromDB(String passport) {
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                stmt = connection.prepareCall("{call get_playerinfo(?)}");
                stmt.setString(1, passport);
                rs = stmt.executeQuery();

                if (rs.next()) {
                    return new PlayerInfo(passport,rs.getInt("cid"),rs.getInt("forbiddenTalkEndTime"),rs.getInt("forbiddenLoginEndTime"));
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
            return null;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return null;
    }

     public boolean  insertPlayerInfo(String passport, int maxCid) {
        CallableStatement stmt = null;
        int rs = 0;
        try {
            if (connectToDB()) {
                stmt = connection.prepareCall("{call insert_playerinfo(?,?)}");
                stmt.setString(1, passport);
                stmt.setInt(2, maxCid);
                rs = stmt.executeUpdate();

                if (rs == 1) {
                    return true;
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return false;
    }

     public boolean getMaxCid(WebServer webServer) {
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                stmt = connection.prepareCall("{call get_maxcid()}");
                rs = stmt.executeQuery();
                if(rs.next()){
                    webServer.maxCid = rs.getInt("maxcid") + 1;
                } else {
                    webServer.maxCid = 0;
                }
                return true;
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return false;
    }

     @SuppressWarnings("resource")
	public boolean  firstlogin( String passport, int serverId, int remoteIP) {
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                // 调用存储过程
                stmt = connection.prepareCall("{call get_firstlogin(?,?)}");
                stmt.setString(1, passport);
                stmt.setInt(2, serverId);
                rs = stmt.executeQuery();
                if(!rs.first()){
                    stmt = connection.prepareCall("{call insert_firstlogin(?,?,?)}");
                    stmt.setString(1, passport);
                    stmt.setInt(2, serverId);
                    stmt.setInt(3, remoteIP);
                    if(stmt.executeUpdate() == 1){
                        return true;
                    }
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return false;
    }

     public boolean  getTop10ByLevel(TopLevelPlayer[] topLevelPlayers) {
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                stmt = connection.prepareCall("{call gettopplayerbylevel()}");
                rs = stmt.executeQuery();
                int count = 0;
                while(rs.next() && count < topLevelPlayers.length){
                    topLevelPlayers[count] = new TopLevelPlayer(rs.getInt("mid"),rs.getString("name"),rs.getInt("gender"),rs.getInt("job"),rs.getInt("level"));
                    count++;
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return false;
    }

     public List getServersHasLogin(String passport) {
        CallableStatement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                stmt = connection.prepareCall("{call getserviceshaslogin(?)}");
                stmt.setString(1, passport);
                List<Integer> serverList = new ArrayList<Integer>();
                rs = stmt.executeQuery();
                while(rs.next()){
                    serverList.add(rs.getInt("serverId"));
                }
                return serverList;
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return null;
    }


    public void dbKeepAlive() {
        Statement stmt = null;
        try {
            if (connectToDB()) {
                stmt = connection.createStatement();
                stmt.executeQuery(Consts.DB_KEEPALIVE_TEST_STATEMENT);
            }
        } catch (SQLException ex) {
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
    }

    public int getCidByPlayerName(String playerName) {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                String sql = "SELECT mid from charactor WHERE name like '" + playerName + "';";
                stmt = connection.createStatement();

                rs = stmt.executeQuery(sql);

                if (rs.next()) {
                    return rs.getInt("mid");
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return -1;
    }

    public String getPassportByCid(int cid) {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                String sql = "SELECT thirdPartyUserId from thirdpartyuser WHERE cid = " + cid + ";";
                stmt = connection.createStatement();

                rs = stmt.executeQuery(sql);

                if (rs.next()) {
                    return rs.getString("thirdPartyUserId");
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return null;
    }

    public int getCidByPassport(String passport) {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                String sql = "SELECT cid from thirdpartyuser WHERE thirdPartyUserId = '" + passport + "';";
                stmt = connection.createStatement();

                rs = stmt.executeQuery(sql);

                if (rs.next()) {
                    return rs.getInt("cid");
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return -1;
    }

    public HashSet<String> getTrustedConsoleHosts() {
        HashSet<String> ips = new HashSet<String>();

        Statement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                String sql = "SELECT ip FROM trustedconsoleips;";
                stmt = connection.createStatement();

                rs = stmt.executeQuery(sql);

                while (rs.next()) {
                    ips.add(rs.getString("ip"));
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return ips;
    }

    public ServerConfig getServerConfig(int id) {
        ServerConfig retServerConfig = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                String sql = "SELECT id,regionid,name,dbhost,dbname,logdbhost,logdbnameprefix,host,externalhost,externalport,managerport,rpcport,billServerGetAssetAddress,billServerTransactionAddress,couponServerApplyAddress,billApiKey,billApiSecret,opentime,jvmoption FROM serverconfig WHERE id=" + id +";";
                stmt = connection.createStatement();

                rs = stmt.executeQuery(sql);

                if (rs.next()) {
                    retServerConfig = new ServerConfig();
                    retServerConfig.id = rs.getInt("id");
                    retServerConfig.regionId = rs.getString("regionid");
                    retServerConfig.name = rs.getString("name");
                    retServerConfig.dbName = rs.getString("dbname");
                    retServerConfig.dbHost = rs.getString("dbhost");
                    retServerConfig.logDBHost = rs.getString("logdbhost");
                    retServerConfig.logDBNamePrefix = rs.getString("logdbnameprefix");
                    retServerConfig.host = rs.getString("host");
                    retServerConfig.externalHost = rs.getString("externalhost");
                    retServerConfig.externalPort = rs.getInt("externalport");
                    retServerConfig.managerPort = rs.getInt("managerport");
                    retServerConfig.rpcPort = rs.getInt("rpcport");
                    retServerConfig.billServerGetAssetAddress = rs.getString("billServerGetAssetAddress");
                    retServerConfig.billServerTransactionAddress = rs.getString("billServerTransactionAddress");
                    retServerConfig.couponServerApplyAddress = rs.getString("couponServerApplyAddress");
                    retServerConfig.billApiKey = rs.getString("billApiKey");
                    retServerConfig.billApiSecret = rs.getString("billApiSecret");
                    Timestamp ts = rs.getTimestamp("opentime");
                    retServerConfig.openTime = new Date(ts.getTime());
                    retServerConfig.jvmOption = rs.getString("jvmoption");
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return retServerConfig;
    }

    /*
        获取服务配置信息
    */
    public LinkedList<ServerConfig> getServerConfigs() {
        LinkedList<ServerConfig> serverConfigs = new LinkedList<ServerConfig>();

        Statement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                String sql = "SELECT id,regionid,name,dbhost,dbname,logdbhost,logdbnameprefix,host,externalhost,externalport,managerport,rpcport,billServerGetAssetAddress,billServerTransactionAddress,couponServerApplyAddress,billApiKey,billApiSecret,opentime,jvmoption FROM serverconfig;";
                stmt = connection.createStatement();

                rs = stmt.executeQuery(sql);

                while (rs.next()) {
                    ServerConfig serverConfig = new ServerConfig();
                    serverConfig.id = rs.getInt("id");
                    serverConfig.regionId = rs.getString("regionid");
                    serverConfig.name = rs.getString("name");
                    serverConfig.dbName = rs.getString("dbname");
                    serverConfig.dbHost = rs.getString("dbhost");
                    serverConfig.logDBHost = rs.getString("logdbhost");
                    serverConfig.logDBNamePrefix = rs.getString("logdbnameprefix");
                    serverConfig.host = rs.getString("host");
                    serverConfig.externalHost = rs.getString("externalhost");
                    serverConfig.externalPort = rs.getInt("externalport");
                    serverConfig.managerPort = rs.getInt("managerport");
                    serverConfig.rpcPort = rs.getInt("rpcport");
                    serverConfig.billServerGetAssetAddress = rs.getString("billServerGetAssetAddress");
                    serverConfig.billServerTransactionAddress = rs.getString("billServerTransactionAddress");
                    serverConfig.couponServerApplyAddress = rs.getString("couponServerApplyAddress");
                    serverConfig.billApiKey = rs.getString("billApiKey");
                    serverConfig.billApiSecret = rs.getString("billApiSecret");
                    Timestamp ts = rs.getTimestamp("opentime");
                    serverConfig.openTime = new Date(ts.getTime());
                    serverConfig.jvmOption = rs.getString("jvmoption");
                    serverConfigs.add(serverConfig);
                }
            }
        } catch (SQLException ex) {
            // TODO: 统计DB出错次数,当连续出错次数达到限值时,表示数据库故障
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return serverConfigs;
    }

    public DBCharInfo getUserDetailInfo(int playerId) {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            if (connectToDB()) {
                String sql = "select mid,name,gender,job,level from charactor where mid =" + playerId + ";";
                stmt = connection.createStatement();
                rs = stmt.executeQuery(sql);
                DBCharInfo charInfo = new DBCharInfo();
                if (rs.next()) {
                    charInfo.mid = rs.getInt("mid");
                    charInfo.name = rs.getString("name");
                    charInfo.gender = rs.getInt("gender");
                    charInfo.job = rs.getInt("job");
                    charInfo.level = rs.getInt("level");
                   
                    return charInfo;
                } else {
                    MLogger.getlogger().debuglog(LogConsts.LOGLEVEL_ERROR, "Player[" + playerId + "] not found in database.");
                }
            }
        } catch (SQLException ex) {
            MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    MLogger.getlogger().log(LogConsts.LOGTYPE_DBERR, StackTraceUtil.getStackTrace(ex));
                }
            }
        }
        return null;
    }
}
